package cn.easy.kitchen.service;

import cn.easy.base.bean.PageBean;
import cn.easy.base.service.BaseService;
import cn.easy.base.utils.jdbc.PaginationHelper;
import cn.easy.kitchen.bean.StatNutritionBean;
import cn.easy.kitchen.domain.StatNutrition;
import cn.easy.kitchen.repository.StatNutritionDao;
import cn.easy.kitchen.utils.ProjectUtil;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Component
public class StatNutritionService extends BaseService<StatNutrition> {
    @Autowired
    private StatNutritionDao	statNutritionDao;
    @Autowired
    private JdbcTemplate jdbcTemplate;


    public Page<StatNutrition> search(Map<String, Object> searchParams, PageBean pageBean) {
        return statNutritionDao.findAll(spec(searchParams), pageBean.toPageRequest(new Sort(Direction.DESC, "createdAt")));
    }

    public Page<StatNutrition> pageByParams(Map<String, Object> searchParams, PageBean pageBean) {
        List<Object> args = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT DISTINCT cc.name as name, SUM(cn.protein) as protein_sum,sum(cn.fat) as fat_sum,sum(cn.sugar) as sugar_sum, ");
        sql.append("sum(cn.dietary_Fiber) as dietary_Fiber_sum, sum(cn.trace_Element) as trace_Element_sum  from ck_stat_nutrition cn ");
        sql.append("LEFT JOIN ck_customer cc on cn.customer_id = cc.id ");

        sql.append("where cn.data_on >= ? ");
        args.add(ProjectUtil.formatDate(searchParams.get("GTE_dataOn")));

        sql.append("and cn.data_on <= ? ");
        args.add(ProjectUtil.formatDate(searchParams.get("LTE_dataOn")));

        sql.append("and cc.name is not null ");
        sql.append("GROUP BY cn.customer_Id ");
        sql.append("order by (SUM(protein) + sum(fat) + if(isnull(sum(sugar)),0,sum(sugar)) + sum(dietary_Fiber) + sum(trace_Element)) desc ");

        PaginationHelper<StatNutrition> helper = new PaginationHelper<>();
        return helper.fetchPage(jdbcTemplate, sql.toString(), args.toArray(), pageBean, new RowMapper<StatNutrition>() {
            @Override
            public StatNutrition mapRow(ResultSet rs, int i) throws SQLException {
                StatNutrition bean = new StatNutrition();
                bean.setCustomerId(ProjectUtil.cutStrLength(rs.getString("name"), 6));
                bean.setProtein(rs.getBigDecimal("protein_sum"));
                bean.setFat(rs.getBigDecimal("fat_sum"));
                bean.setSugar(rs.getBigDecimal("sugar_sum"));
                bean.setDietaryFiber(rs.getBigDecimal("dietary_Fiber_sum"));
                bean.setTraceElement(rs.getBigDecimal("trace_Element_sum"));
                return bean;
            }
        });
    }

    public StatNutritionBean searchByParams(Map<String, Object> searchParams) {
        StringBuilder sql = new StringBuilder("select sum(protein), sum(fat), sum(sugar), sum(dietary_Fiber), sum(trace_Element), ");
        sql.append("AVG(protein),AVG(fat),AVG(sugar),AVG(dietary_Fiber),AVG(trace_Element) ");
        sql.append("from ck_stat_nutrition where ");
        sql.append("data_on >=:start_dataOn ");
        sql.append("and data_on <=:end_dataOn ");
        if (ProjectUtil.isValidParam(searchParams, "EQ_customerId")) {
            sql.append("and customer_Id =:customerId");
        }
        Query query = entityManager.createNativeQuery(sql.toString());
        query.setParameter("start_dataOn", ProjectUtil.formatDate(searchParams.get("GTE_dataOn")));
        query.setParameter("end_dataOn", ProjectUtil.formatDate(searchParams.get("LTE_dataOn")));
        if (ProjectUtil.isValidParam(searchParams, "EQ_customerId")) {
            query.setParameter("customerId", searchParams.get("EQ_customerId"));
        }
        List<Object[]> list = query.getResultList();
        String nameArray[] = new String[]{"蛋白质", "脂肪", "糖", "膳食纤维", "微量元素"};
        BigDecimal sumArray[] = new BigDecimal[5];
        BigDecimal avgArray[] = new BigDecimal[5];
        if (list != null && list.size() > 0) {
            Object[] ob = list.get(0);
            if (ob[0] != null) {
                sumArray[0] = ProjectUtil.getBigDecimalVal(ob[0]);
                sumArray[1] = ProjectUtil.getBigDecimalVal(ob[1]);
                sumArray[2] = ProjectUtil.getBigDecimalVal(ob[2]);
                sumArray[3] = ProjectUtil.getBigDecimalVal(ob[3]);
                sumArray[4] = ProjectUtil.getBigDecimalVal(ob[4]);

                avgArray[0] = ProjectUtil.getBigDecimalVal(ob[5]);
                avgArray[1] = ProjectUtil.getBigDecimalVal(ob[6]);
                avgArray[2] = ProjectUtil.getBigDecimalVal(ob[7]);
                avgArray[3] = ProjectUtil.getBigDecimalVal(ob[8]);
                avgArray[4] = ProjectUtil.getBigDecimalVal(ob[9]);
            }
        }
        StatNutritionBean statNutritionBean = new StatNutritionBean();
        statNutritionBean.setNameArray(nameArray);
        statNutritionBean.setSumArray(sumArray);
        statNutritionBean.setAvgArray(avgArray);
        return statNutritionBean;
    }

    @Transactional(propagation = Propagation.REQUIRED)
    public void delete(String id) {
        statNutritionDao.delete(id);
    }

    @Transactional(propagation = Propagation.REQUIRED)
    public void deleteAll() {
        statNutritionDao.deleteAll();
    }

    public StatNutrition getOne(String id) {
        if (StringUtils.isBlank(id)) {
            return null;
        }
        return statNutritionDao.findOne(id);
    }

    public List<StatNutrition> findAll(List<String> idList) {
        return Lists.newArrayList(statNutritionDao.findAll(idList));
    }

    @Transactional(propagation = Propagation.REQUIRED)
    public StatNutrition save(StatNutrition statNutrition) {
        return statNutritionDao.save(statNutrition);
    }

    @Transactional(propagation = Propagation.REQUIRED)
    public void save(List<StatNutrition> statNutritionList) {
        statNutritionDao.save(statNutritionList);
    }
}


